package dao;

import model.Conn;
import model.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    Conn coon = new Conn();

    /**
     * 添加用户
     *
     * @param user 用户信息
     * @return Boolean
     * @throws SQLException
     */
    public Boolean addUser(User user) throws SQLException {
        String sql = "insert into user(username,email,password,name,phone,address,isadmin,isvalidate) values(?,?,?,?,?,?,?,?)";
        connection = coon.getConn();

        ps = connection.prepareStatement(sql);
        ps.setString(1, user.getUsername());
        ps.setString(2, user.getEmail());
        ps.setString(3, user.getPassword());
        ps.setString(4, user.getName());
        ps.setString(5, user.getPhone());
        ps.setString(6, user.getAddress());
        ps.setBoolean(7, user.isIsadmin());
        ps.setBoolean(8, user.isIsvalidate());
        int i = ps.executeUpdate();
        Conn.ClossAll(connection, ps, rs);
        return i > 0;

    }

    /**
     * 查找表中是否已有用户
     *
     * @param username 用户名
     * @return boolean
     * @throws SQLException
     */
    public boolean isUsernameExist(String username) throws SQLException {
        String sql = "select * from user where username = ? ";
        connection = coon.getConn();

        ps = connection.prepareStatement(sql);
        ps.setString(1, username);
        boolean flag = rs.next();
        Conn.ClossAll(connection, ps, rs);
        return flag;
    }

    /**
     * 查找表中是否已有邮箱
     *
     * @param email 邮箱
     * @return boolean
     * @throws SQLException
     */
    public boolean isEmailExist(String email) throws SQLException {
        String sql = "select * from user where email = ?";
        connection = coon.getConn();
        ps = connection.prepareStatement(sql);
        ps.setString(1, email);
        rs = ps.executeQuery();
        boolean flag = rs.next();
        Conn.ClossAll(connection, ps, rs);
        return flag;
    }


    /**
     * 查找用户通过用户名和密码
     *
     * @param username 用户名
     * @param password 密码
     * @return User || null
     * @throws SQLException
     */
    public User selectByUsernamePassword(String username, String password) throws SQLException {
        String sql = "select * from user where username=? and password=?";
        connection = coon.getConn();
        ps = connection.prepareStatement(sql);
        ps.setString(1, username);
        ps.setString(2, password);
        rs = ps.executeQuery();
        User user = null;
        if (rs.next()) {
            user = new User();
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setId(Integer.parseInt(rs.getString("id")));
            user.setEmail(rs.getString("email"));
            user.setName(rs.getString("name"));
            user.setIsadmin(rs.getBoolean("isadmin"));
            user.setIsvalidate(rs.getBoolean("isvalidate"));
            user.setAddress(rs.getString("address"));
            user.setPhone(rs.getString("phone"));
        }
        Conn.ClossAll(connection, ps, rs);
        return user;
    }

    /**
     * 查找用户和密码通过邮箱和密码
     *
     * @param email    邮箱
     * @param password 密码
     * @return User || null
     * @throws SQLException
     */
    public User selectByEmailPassword(String email, String password) throws SQLException {
        String sql = "select * from user where email=? and password=?";
        connection = coon.getConn();
        ps = connection.prepareStatement(sql);
        ps.setString(1, email);
        ps.setString(2, password);
        rs = ps.executeQuery();
        User user = null;
        if (rs.next()) {
            user = new User();
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setId(Integer.parseInt(rs.getString("id")));
            user.setEmail(rs.getString("email"));
            user.setName(rs.getString("name"));
            user.setIsadmin(rs.getBoolean("isadmin"));
            user.setIsvalidate(rs.getBoolean("isvalidate"));
            user.setAddress(rs.getString("address"));
            user.setPhone(rs.getString("phone"));
        }
        Conn.ClossAll(connection, ps, rs);
        return user;
    }

    /**
     * 更新用户密码
     *
     * @param user 用户信息对象
     * @return boolean
     * @throws SQLException
     */
    public boolean updatePwd(User user) throws SQLException {
        String sql = "update user set password = ? where id = ?";
        connection = coon.getConn();
        ps = connection.prepareStatement(sql);
        ps.setString(1, user.getPassword());
        ps.setInt(2, user.getId());

        int i = ps.executeUpdate();
        Conn.ClossAll(connection, ps, rs);
        return i > 0;

    }

    /**
     * 更新用户地址 电话 和 姓名
     *
     * @param user 用户信息对象
     * @return boolean
     * @throws SQLException
     */
    public boolean updateUserAddress(User user) throws SQLException {
        String sql = "update user set address = ? , phone = ? , name = ? where id = ?";
        connection = coon.getConn();
        ps = connection.prepareStatement(sql);
        ps.setString(1, user.getAddress());
        ps.setString(2, user.getPhone());
        ps.setString(3, user.getName());
        ps.setInt(4, user.getId());
        int i = ps.executeUpdate();
        Conn.ClossAll(connection, ps, rs);
        return i > 0;
    }

    /**
     * 删除用户通过id
     *
     * @param id 用户id
     * @return boolean
     * @throws SQLException
     */
    public boolean deleteUser(int id) throws SQLException {
        String sql = "DELETE FROM `cakeshop`.`user` WHERE `id` = ?";
        connection = coon.getConn();
        ps = connection.prepareStatement(sql);
        ps.setInt(1, id);
        int i = ps.executeUpdate();
        Conn.ClossAll(connection, ps, rs);
        return i > 0;
    }

    /**
     * 获取指定页用户
     *
     * @param pageNumber 第几页
     * @param pageSize   一页多少条数据
     * @return
     * @throws SQLException
     */
    public List<User> selectAllUser(int pageNumber, int pageSize) throws SQLException {
        List<User> userList = new ArrayList<>();
        String sql = "SELECT * FROM `cakeshop`.`user` LIMIT ?,?";
        connection = coon.getConn();
        ps = connection.prepareStatement(sql);
        ps.setInt(1, pageNumber);
        ps.setInt(2, pageSize);
        rs = ps.executeQuery();
        while (rs.next()) {
            User user = new User();
            user.setId(rs.getInt("id"));
            user.setUsername(rs.getString("username"));
            user.setEmail(rs.getString("email"));
            user.setPassword(rs.getString("password"));
            user.setName(rs.getString("name"));
            user.setPhone(rs.getString("phone"));
            user.setAddress(rs.getString("address"));
            user.setIsadmin(rs.getBoolean("isadmin"));
            user.setIsvalidate(rs.getBoolean("isadmin"));
            userList.add(user);
        }
        Conn.ClossAll(connection, ps, rs);
        return userList;
    }

    /**
     * 获取用户数量
     *
     * @return int 用户数量
     * @throws SQLException
     */
    public int getUserCount() throws SQLException {
        int goodsCount = 0;
        String sql = "select count(*) acc from `user`";
        ps = coon.getConn().prepareStatement(sql);
        rs = ps.executeQuery();
        rs.next();
        goodsCount = rs.getInt("acc");
        Conn.ClossAll(connection, ps, rs);
        return goodsCount;
    }

    /**
     * 获取用户信息通过id
     *
     * @param id 用户id
     * @return User || null
     * @throws SQLException
     */
    public User getUserById(int id) throws SQLException {
        String sql = "select * from `user` where `id` = ?";
        connection = coon.getConn();
        ps = connection.prepareStatement(sql);
        ps.setInt(1, id);
        rs = ps.executeQuery();
        User user = new User();
        if (rs.next()) {
            user = new User();
            user.setId(rs.getInt("id"));
            user.setUsername(rs.getString("username"));
            user.setEmail(rs.getString("email"));
            user.setPassword(rs.getString("password"));
            user.setName(rs.getString("name"));
            user.setPhone(rs.getString("phone"));
            user.setAddress(rs.getString("address"));
            user.setIsadmin(rs.getBoolean("isadmin"));
            user.setIsvalidate(rs.getBoolean("isvalidate"));
        }
        Conn.ClossAll(connection, ps, rs);
        return user;
    }
}
